CREATE EXTERNAL TABLE jms_dwd.dwd_oms_waybill_extend_base_dt
(
    id                      bigint COMMENT 'id',
    waybill_no              string COMMENT '运单号',
    package_name            timestamp COMMENT '集包地名称',
    package_code            string COMMENT '集包地code',
    input_time              timestamp COMMENT '录入时间',
    collection_package_type tinyint COMMENT '集包类型, 1:网点集包, 2:中心集包, 3:集散点集包',
    is_privacy              tinyint COMMENT '是否私密面单:1是 0否',
    fourth_segment_code     string COMMENT '第四段码'
)
    COMMENT '运单表扩展清洗表'
    PARTITIONED BY (dt string COMMENT '时间分区-input_time')
    ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
        WITH SERDEPROPERTIES (
        'serialization.format' = '1'
        )
    STORED AS
        INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
        OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
    LOCATION '/dw/hive/jms_dwd.db/external/dwd_oms_waybill_extend_base_dt'
;

-- add columns by kumiler on 2023-08-11
alter table jms_dwd.dwd_oms_waybill_extend_base_dt
add columns (
transport_type_code string comment '运输类型编号',
destination_source_code int comment '目的地来源编号',
pick_parent_network_code string comment '一级（所属）网点编号',
pick_parent_network_name string comment '一级（所属）网点名称',
settle_customer_code string comment '结算客户编号',
settle_customer_name string comment '结算客户名称',
receiver_privacy_phone string comment '收件人隐私手机号',
self_vip int comment '自有客户 5-vip 6-菜鸟vip',
customer_label string comment '客户打标',
on_demand int comment '按需配送打标',
marketing_activity_type_name string comment '市场活动类型名称',
marketing_activity_type_code string comment '市场活动类型code',
fourth_address_id bigint comment '四级地址id',
fourth_address_name string comment '四级地址'
    ) cascade ;